if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CloneCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CloneCOI]
GO

Create procedure [dbo].[CloneCOI]
(
	@oldCOIId INT,
	@reportingPeriodId int
)
AS

BEGIN
	DECLARE @TempQA TABLE (
	[RowNum] [int] IDENTITY(1,1) NOT NULL,
	[ID] [int] NOT NULL,
	[QuestionnaireVersionId] [int] NOT NULL,
	[Name] [varchar] (256) NOT NULL,
	[Required] [bit] NOT NULL DEFAULT 0,
	[Editable] [bit] NOT NULL DEFAULT 0,
	[LastModifiedDate] DATETIME NULL ,
	[ExternalId] [int] NOT NULL DEFAULT 0,
	[Highlight] [bit] NOT NULL DEFAULT 0
	)

	DECLARE @newCOIId INT

	INSERT INTO ConflictOfInterest(AdmPersonId, FwkDomainUserId, LastNotified, AggregateEquity, HasOutsideActivities, HasEquityInterests, HasFamilyAssociations, Royalty, OtherActivity, PolicyViolation, Status, ReportingPeriodId)
	SELECT AdmPersonId, FwkDomainUserId, GetDate(), AggregateEquity, HasOutsideActivities, HasEquityInterests, HasFamilyAssociations, Royalty, OtherActivity, PolicyViolation, 'COISTATUS_START', @reportingPeriodId
	FROM ConflictOfInterest
	WHERE Id = @oldCOIId

	SELECT @newCOIId = IDENT_CURRENT('ConflictOfInterest')

	
	

	DECLARE @recordCount INT
	DECLARE @recordIndex INT
	DECLARE @oldQAId INT
	DECLARE @newQAId INT

	SET @recordIndex = 1


	--insert all the corresponding questionnaire answers in temp table
	INSERT INTO @TempQA (ID, QuestionnaireVersionId, Name, Required, Editable, LastModifiedDate, ExternalId, Highlight)
	SELECT Id, QuestionnaireVersionId, Name, Required, Editable, LastModifiedDate, ExternalId, Highlight
	FROM QuestionnaireAnswer QA INNER JOIN COI_QuestionnaireAnswer_Map Map ON QA.Id = Map.QuestionnaireAnswerId
	WHERE Map.COIId = @oldCOIId

	SELECT @recordCount = count(*) from @TempQA 

	/*insert each record in temp table into QuestionnaireAnswer, insert the new mapping record and insert all the Answer records recursively */
	WHILE (@recordIndex <= @recordCount)
	BEGIN
		SELECT @oldQAId = [Id] FROM @TempQA WHERE RowNum = @recordIndex

		
		INSERT INTO QuestionnaireAnswer (QuestionnaireVersionId, Name, Required, Editable, LastModifiedDate, ExternalId, Highlight, Status)
		SELECT QuestionnaireVersionId, Name, Required, Editable, LastModifiedDate, ExternalId, Highlight, 'QASTATUS_START'
		FROM @TempQA 
		WHERE RowNum = @recordIndex

		SELECT @newQAId = IDENT_CURRENT('QuestionnaireAnswer')

		INSERT INTO COI_QuestionnaireAnswer_Map (COIId, QuestionnaireAnswerId) VALUES (@newCOIId, @newQAId)

		Exec CloneAnswers 0, 0, @newQAId, @oldQAId

		SET @recordIndex = @recordIndex + 1
	END	
END